#include "duiwujibenqingkuang02.h"
#include "ui_duiwujibenqingkuang02.h"

#include<QtSql>
#include<QtGui>
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlTableModel>
#include <QSqlError>
#include <QDebug>

duiwujibenqingkuang02::duiwujibenqingkuang02(QWidget *parent) :
    QDialog(parent),
    ui(new Ui::duiwujibenqingkuang02)
{
    ui->setupUi(this);


}

duiwujibenqingkuang02::~duiwujibenqingkuang02()
{
    delete ui;
}

void duiwujibenqingkuang02::on_pushButton_clicked()
{

    QSqlQuery query;

    query.exec("SELECT COUNT(PoliticID) FROM CadreInfo WHERE PoliticID='01'");//党员代码为1
    if(query.next()){
        QString text = query.value(0).toString();
        ui->dynum->setText(text);
    }

    //在岗党员数量
    query.exec("SELECT COUNT(PoliticID) FROM CadreInfo WHERE PoliticID='01' and ManageStatusID='1'");//党员代码为1
    if(query.next()){
        QString text = query.value(0).toString();
        ui->zgdynum->setText(text);
    }

    //在岗党员占比
    query.exec("    SELECT CONCAT(ROUND(T1.co/T2.totalCo*100,1),'%')FROM (SELECT COUNT(PoliticID) AS co FROM CadreInfo WHERE PoliticID='01' and ManageStatusID='1') T1,(SELECT COUNT(PoliticID) AS totalCo FROM CadreInfo WHERE PoliticID='01')T2;");//少数民族党员
    if(query.next()){
        QString text = query.value(0).toString();
        ui->zgdyrate->setText(text);
        }

    //离退休党员数量
    query.exec("SELECT COUNT(PoliticID) FROM CadreInfo WHERE PoliticID='01' and ManageStatusID='0'");//党员代码为1
    if(query.next()){
        QString text = query.value(0).toString();
        ui->txdynum->setText(text);
    }


    //离退休党员占比
    query.exec("    SELECT CONCAT(ROUND(T1.co/T2.totalCo*100,1),'%')FROM (SELECT COUNT(PoliticID) AS co FROM CadreInfo WHERE PoliticID='01' and ManageStatusID='0') T1,(SELECT COUNT(PoliticID) AS totalCo FROM CadreInfo WHERE PoliticID='01')T2;");//少数民族党员
    if(query.next()){
        QString text = query.value(0).toString();
        ui->txdyrate->setText(text);
        }

    //大专党员数量
        query.exec("SELECT COUNT(1) FROM Edu,CadreInfo WHERE  Edu.IDnum=CadreInfo.IDnum AND PoliticID='01' AND Eduname<'32'");//大专以上
        if(query.next()){
            QString text = query.value(0).toString();
            ui->dzdynum->setText(text);

        }
    //大专党员占比
        query.exec("    SELECT CONCAT(ROUND(T1.co/T2.totalCo*100,1),'%')FROM (SELECT COUNT(1) AS co FROM Edu,CadreInfo WHERE  Edu.IDnum=CadreInfo.IDnum AND PoliticID='01' AND Eduname<'32') T1,(SELECT COUNT(PoliticID) AS totalCo FROM CadreInfo WHERE PoliticID='01')T2;");//少数民族党员
        if(query.next()){
            QString text = query.value(0).toString();
            ui->dzdyrate->setText(text);
            }


        //中专党员数量
        query.exec("SELECT COUNT(1) FROM Edu,CadreInfo WHERE  Edu.IDnum=CadreInfo.IDnum AND PoliticID='1' AND Eduname>'39' ");//专业代码待改
        if(query.next()){
            QString text = query.value(0).toString();
            ui->zzdynum->setText(text);

        }
        //中专党员占比
        query.exec("    SELECT CONCAT(ROUND(T1.co/T2.totalCo*100,1),'%')FROM (SELECT COUNT(1) AS co FROM Edu,CadreInfo WHERE  Edu.IDnum=CadreInfo.IDnum AND PoliticID='01' AND Eduname>'39') T1,(SELECT COUNT(PoliticID) AS totalCo FROM CadreInfo WHERE PoliticID='01')T2;");//少数民族党员
        if(query.next()){
            QString text = query.value(0).toString();
            ui->zzdyrate->setText(text);
        }



        //35岁以下党员数量
        query.exec("SELECT COUNT(1) FROM CadreInfo WHERE TIMESTAMPDIFF(YEAR,Birth,NOW()) <35 AND PoliticID='01' ");//35以下党员
        if(query.next()){
            QString text = query.value(0).toString();
            ui->swdynum->setText(text);
            }
        query.exec("    SELECT CONCAT(ROUND(T1.co/T2.totalCo*100,1),'%')FROM (SELECT COUNT(1) AS co FROM CadreInfo WHERE TIMESTAMPDIFF(YEAR,Birth,NOW()) < 35 AND PoliticID='01') T1,(SELECT COUNT(PoliticID) AS totalCo FROM CadreInfo WHERE PoliticID='01')T2;");//少数民族党员
        if(query.next()){
            QString text = query.value(0).toString();
            ui->swdyrate->setText(text);
            }

        //60岁以下党员数量
        query.exec("SELECT COUNT(1) FROM CadreInfo WHERE TIMESTAMPDIFF(YEAR,Birth,NOW())  <60 AND TIMESTAMPDIFF(YEAR,Birth,NOW()) >36 and PoliticID='01' ");//35以下党员
        if(query.next()){
            QString text = query.value(0).toString();
            ui->lldynum->setText(text);
            }
        query.exec("    SELECT CONCAT(ROUND(T1.co/T2.totalCo*100,1),'%')FROM (SELECT COUNT(1) AS co FROM CadreInfo WHERE TIMESTAMPDIFF(YEAR,Birth,NOW())  <60 AND TIMESTAMPDIFF(YEAR,Birth,NOW()) >36 and PoliticID='01') T1,(SELECT COUNT(PoliticID) AS totalCo FROM CadreInfo WHERE PoliticID='01')T2;");//少数民族党员
        if(query.next()){
            QString text = query.value(0).toString();
            ui->lldyrate->setText(text);
            }
        //61岁以上党员数量及比例
        query.exec("SELECT COUNT(1) FROM CadreInfo WHERE TIMESTAMPDIFF(YEAR,Birth,NOW())>61 AND PoliticID='01' ");//35以下党员
        if(query.next()){
            QString text = query.value(0).toString();
            ui->lydynum->setText(text);
            }
        query.exec("    SELECT CONCAT(ROUND(T1.co/T2.totalCo*100,1),'%')FROM (SELECT COUNT(1) AS co FROM CadreInfo WHERE TIMESTAMPDIFF(YEAR,Birth,NOW()) >61 AND PoliticID='01') T1,(SELECT COUNT(PoliticID) AS totalCo FROM CadreInfo WHERE PoliticID='01')T2;");//少数民族党员
        if(query.next()){
            QString text = query.value(0).toString();
            ui->lydyrate->setText(text);
            }

}
